﻿using BCCommon;
using BCEntity.TMS.VehicleType;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using BCEntity.TMS.Agent.AgnetVehicleType;

namespace BCData.TMS.Agent.AgnetVehicleType
{
    public class AgentVehicleTypeData : IAgentVehicleTypeData
    {
        private readonly IDatabaseContext database;

        public AgentVehicleTypeData(IDatabaseContext database)
        {
            this.database = database;
        }

        private const string QUERY = @"SELECT AgentVehicleTypeId,AgentRecordId,VehicleTypeId,Enabled,CreateTime,UpdateTime 
                                        FROM AgentVehicleType";

        public async Task<(IEnumerable<VehicleTypeExpandEntity>, int)> GetAsync(long? agentRecordId, string categoryReferenceIdStr, int? vehicleTypeId, string vehicleTypeEnum, 
            int? maxStartWeight, int? maxEndWeight, decimal? maxStartStere, decimal? maxEndStere, bool? enabled, int pageIndex, int pageSize)
        {
            string sql = @"SELECT
	                    A.VehicleTypeId,
	                    A.VehiclePicPath,
	                    A.VehicleTypeName,
	                    A.VehicleTypeEnum,
	                    A.AppliedTo,
	                    A.VehicleLength,
	                    A.VehicleWidth,
	                    A.VehicleHeight,
	                    A.VehicleLengthRatio,
	                    A.VehicleWidthRatio,
	                    A.VehicleHeightRatio,
	                    A.VehicleWheelbase,
	                    A.MaxWeight,
	                    A.MaxStere,
	                    CASE WHEN A.Enabled = 0 THEN A.Enabled ELSE (CASE WHEN D.Enabled IS NULL THEN A.Enabled ELSE D.Enabled END) END AS Enabled,
	                    A.CreateTime,
	                    A.UpdateTime,
	                    A.Editer,
						A.OilConsumption,
						A.OilConsumptionPrice,
						A.EverdayProfit,
	                    B.ItemValue AS VehicleTypeValue,
	                    C.VehicleCategoryReferenceIdStr
                    FROM
	                    VehicleType A
	                    LEFT JOIN DictionaryReference B ON A.VehicleTypeEnum = B.SearchKey
	                    LEFT JOIN (
                                SELECT GROUP_CONCAT( VehicleCategoryReferenceId ) VehicleCategoryReferenceIdStr,VehicleTypeId
                                    FROM VehicleCategoryReference
                                    GROUP BY VehicleTypeId
                        ) C
	                    ON A.VehicleTypeId = C.VehicleTypeId
                        LEFT JOIN AgentVehicleType D ON D.VehicleTypeId = A.VehicleTypeId ";
            var countSql = @" SELECT COUNT(1) FROM
                            VehicleType A
                            LEFT JOIN DictionaryReference B ON A.VehicleTypeEnum = B.SearchKey
                            LEFT JOIN (
                                        SELECT GROUP_CONCAT(VehicleCategoryReferenceId) VehicleCategoryReferenceIdStr,VehicleTypeId
                                            FROM VehicleCategoryReference
                                            GROUP BY VehicleTypeId
                            ) C
                            ON A.VehicleTypeId = C.VehicleTypeId
                            LEFT JOIN AgentVehicleType D ON D.VehicleTypeId = A.VehicleTypeId ";

            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (agentRecordId.HasValue)
            {
                sql = $"{sql} AND D.AgentRecordId = ?AgentRecordId";
                countSql = $"{countSql} AND D.AgentRecordId = ?AgentRecordId";
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (vehicleTypeId.HasValue)
            {
                wheres.Add(" A.VehicleTypeId=?VehicleTypeId ");
                parameters.Add("?VehicleTypeId", vehicleTypeId.Value, DbType.Int32);
            }

            if (!string.IsNullOrWhiteSpace(vehicleTypeEnum))
            {
                wheres.Add(" A.VehicleTypeEnum=?vehicleTypeEnum ");
                parameters.Add("?vehicleTypeEnum", vehicleTypeEnum, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(categoryReferenceIdStr))
            {
                wheres.Add(" FIND_IN_SET(C.VehicleCategoryReferenceIdStr,?VehicleCategoryReferenceIdStr)");
                parameters.Add("?VehicleCategoryReferenceIdStr", categoryReferenceIdStr.Trim(','), DbType.AnsiString);
            }
            if (maxStartWeight.HasValue)
            {
                wheres.Add("A.MaxWeight >= ?MaxStartWeight");
                parameters.Add("?MaxStartWeight", maxStartWeight.Value, DbType.Int32);
            }
            if (maxEndWeight.HasValue)
            {
                wheres.Add("A.MaxWeight <= ?MaxEndWeight");
                parameters.Add("?MaxEndWeight", maxEndWeight.Value, DbType.Int32);
            }
            if (maxStartStere.HasValue)
            {
                wheres.Add("A.MaxStere >= ?MaxStartStere");
                parameters.Add("?MaxStartStere", maxStartStere.Value, DbType.Decimal);
            }
            if (maxEndStere.HasValue)
            {
                wheres.Add("A.MaxStere <= ?MaxEndStere");
                parameters.Add("?MaxEndStere", maxEndStere.Value, DbType.Decimal);
            }

            if (enabled.HasValue)
            {
                wheres.Add("CASE WHEN D.Enabled IS NULL THEN A.Enabled = ?Enabled ELSE D.Enabled = ?Enabled END");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }

            sql = string.Format("{0} {1} LIMIT {3},{4}; {2} {1}",
                sql,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                countSql,
                (pageIndex - 1) * pageSize,
                pageSize);
            return await database.Slave.QueryPageableListAsync<VehicleTypeExpandEntity>(sql, parameters);
        }

        public async Task<AgentVehicleTypeEntity> GetById(long? agentRecordId, int vehicleTypeId)
        {
            return await database.Slave.QueryEntityAsync<AgentVehicleTypeEntity>($"{QUERY} WHERE AgentRecordId = ?AgentRecordId AND VehicleTypeId = ?VehicleTypeId", new { agentRecordId, vehicleTypeId });
        }

        public async Task<AgentVehicleTypeEntity> UpdateAsync(AgentVehicleTypeEntity entity)
        {
            return await database.Master.UpdateAsync(entity, database.Transaction);
        }

        public async Task<AgentVehicleTypeEntity> InsertAsync(AgentVehicleTypeEntity entity)
        {
            return await database.Master.InsertAsync(entity, database.Transaction);
        }

        public async Task<int> UpdateEnabledByVehicleTypeId(int vehicleTypeId)
        {
            return await database.Master.ExecuteAsync("UPDATE AgentVehicleType SET Enabled = 0 WHERE VehicleTypeId = ?VehicleTypeId", new { vehicleTypeId }, database.Transaction);
        }
    }
}
